﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Until
{
    /// <summary>
    /// DB Help
    /// </summary>
    public class DbHelper 
    {
         private string conString;

        /// <summary>
        /// log路径变量
        /// </summary>

         private bool logCan = ConfigurationManager.AppSettings["log"] == null ? false : Convert.ToBoolean(ConfigurationManager.AppSettings["log"]);
         private string filepath = ConfigurationManager.AppSettings["logfilepath"];
         OracleConnection con;
        private void loadconn()
        {
            
                 if (con == null)
                 {
                     con = new OracleConnection(conString);
                 }
                 if (con.State == ConnectionState.Closed)
                 {
                     con.Open();

                 }
        }


        #region 小功能
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        /// <param name="conString"></param>
        public DbHelper()
        {
            conString = ConfigurationManager.AppSettings.Get("conn");
            //conString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            loadconn();
        }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        /// <param name="conString"></param>
        public DbHelper(string conStr)
        {
            conString = conStr;
            loadconn();
        }

        
        #endregion

        #region 执行SQL语句并返回受影响的行数
        /// <summary>
        /// 执行SQL语句并返回受影响的行数
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
           
                int i = -1;
                
                try
                {

                    using (OracleCommand  com = new OracleCommand (sql, con))
                    {
                        i = com.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return i;
        }


        /// <summary>
        /// 执行SQL语句并返回受影响的行数
        /// </summary>
        public int ExecuteNonQuery(string sql, OracleParameter[] par)
        {
            
                int i = -1; 
                try
                {
                    using (OracleCommand  com = new OracleCommand (sql, con))
                    {
                        com.Parameters.AddRange(par);
                        i = com.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }

                return i;
        }
        #endregion

        #region ExecuteReader
        public OracleDataReader ExecuteReader(string sql)
        {

            OracleDataReader reader = null; 
                try
                {
                    using (OracleCommand  com = new OracleCommand (sql, con))
                    {
                        reader = com.ExecuteReader();

                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return reader;
        }
        public OracleDataReader ExecuteReader(string sql, OracleParameter par)
        {
            OracleDataReader reader = null;
          
                con.Open();
                
                try
                {con.Open();
                using (OracleCommand  com = new OracleCommand (sql, con))
                    {
                        com.Parameters.Add(par);
                        reader = com.ExecuteReader();
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return reader;
        }

        #endregion

        #region 读取查询结果中的第一行第一列
        /// <summary>
        /// 读取查询结果中的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
          
                object obj = null;
                try
                {
                    using (OracleCommand  com =new OracleCommand (sql, con))
                    {
                        obj = com.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return obj;
        }

        /// <summary>
        /// 读取查询结果中的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, OracleParameter par)
        {
           
                object obj = null;
                try
                {
                    using (OracleCommand  com =new OracleCommand (sql, con))
                    {
                        com.Parameters.Add(par);
                        obj = com.ExecuteScalar();
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return obj;
        }

        #endregion

        #region 执行Insert语句,并返回新添加的记录ID
        /// <summary>
        /// 执行Insert语句,并返回新添加的记录ID
        /// </summary>
        /// <returns></returns>
        public object ExecuteNonQueryAndGetIdentity(string sql)
        {
           
                object obj = null;
                try
                {
                    using (OracleCommand  com =new OracleCommand (sql, con))
                    {
                        if (com.ExecuteNonQuery() >= 1)
                        {
                            com.CommandText = "select @@identity";
                            obj = com.ExecuteScalar();
                        }
                        else
                        {
                            throw new Exception("记录添加不成功！");
                        }
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                } return obj;
        }

        /// <summary>
        /// 执行Insert语句,并返回新添加的记录ID
        /// </summary>
        public object ExecuteNonQueryAndGetIdentity(string sql, OracleParameter par)
        {
            
                object obj = null;
                try
                {
                    using (OracleCommand  com =new OracleCommand (sql, con))
                    {
                        com.Parameters.Add(par);
                        if (com.ExecuteNonQuery() >= 1)
                        {
                            com.CommandText = "select @@identity";
                            obj = com.ExecuteScalar();
                        }
                        else
                        {
                            throw new Exception("记录添加不成功！");
                        }
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                } return obj;
        }


        #endregion

        #region 返回DataSet
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
           
                DataSet ds = null;
                try
                {
                    
                    using (OracleDataAdapter adpter =new OracleDataAdapter(sql, con))
                     {
                        ds = new DataSet();
                        adpter.Fill(ds);
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                }
                return ds;
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, OracleParameter par)
        {
          
                DataSet ds = null;
                try
                {
                    using (OracleCommand  com = new OracleCommand (sql, con))
                    {
                        com.Parameters.Add(par);
                        
                        using (OracleDataAdapter adpter =new OracleDataAdapter(com))
                        {
                            ds = new DataSet();
                            adpter.Fill(ds);
                        }
                    }
                }
                catch (Exception ex)
                {
                    HandleExceptions(ex);
                }
                finally
                {
                    //可以编写操作日志之类的语句
                } return ds;
        }
        #endregion

        #region 执行多条SQL语句，实现数据库事务
        ///// <summary>  
        ///// 执行多条SQL语句，实现数据库事务。  
        ///// </summary>mysql数据库  
        ///// <param name="SQLStringList">多条SQL语句</param>  
        //public int ExecuteSqlTran(List<string> sqlStringList)
        //{

        //    OracleCommand  cmd = new OracleCommand ();
        //        cmd.Connection = con;
        //        OracleTransaction tx = con.BeginTransaction();
        //        cmd.Transaction = tx;
        //        try
        //        {
        //            for (int n = 0; n < sqlStringList.Count; n++)
        //            {
        //                string strsql = sqlStringList[n].ToString();
        //                if (strsql.Trim().Length > 1)
        //                {
        //                    cmd.CommandText = strsql;
        //                    cmd.ExecuteNonQuery();
        //                }
        //                //后来加上的  
        //                if (n > 0 && (n % 500 == 0 || n == sqlStringList.Count - 1))
        //                {
        //                    tx.Commit();
        //                    tx = con.BeginTransaction();
        //                }
        //            }
        //            return 1;
        //            //tx.Commit();//原来一次性提交  
        //        }
        //        catch (System.Data.SqlClient.SqlException E)
        //        {
        //            tx.Rollback();
        //            throw new Exception(E.Message);
        //        }
               
        //}
        #endregion

        #region ***** 基础--basis *****

        #region 日志相关--basis
        /// <summary>  
        /// Handles the exceptions          
        /// </summary>  
        /// <param name="ex"></param>  
        private void HandleExceptions(Exception ex)
        {
            if (logCan && !string.IsNullOrEmpty(Filepath))
            {
                WriteToLog(ex.Message);
            }
            else
            {
                throw ex;
            }
        }

        /// <summary>
        /// 错误日志Log记录文件路径
        /// </summary>
        public string Filepath
        {
            get
            {
                return filepath;
            }
            set
            {
                if (string.IsNullOrEmpty(value))
                    filepath =@"D:\log.log";
                else
                    filepath = value;
            }
        }

        /// <summary>          
        /// Write to log          
        /// </summary>  
        /// <param name="msg"></param>          
        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(Filepath);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }
        #endregion 
        #endregion


        #region 执行多条SQL语句，实现数据库事务

        /// <summary>
        /// sql语句及参数列表(存放要执行Sql语句及其对应的参数)
        /// </summary>
        public struct sttSqlList
        {
            private string sSQl;
            /// <summary>
            /// SQl语句
            /// </summary>
            public string atbSQl
            {
                get
                {
                    return sSQl;
                }
                set
                {
                    sSQl = value;
                }
            }
            sttSqlParameterList[] splList;
            /// <summary>
            /// 参数列表
            /// </summary>
            public sttSqlParameterList[] atbSqlPraList
            {
                get
                {
                    return splList;
                }
                set
                {
                    splList = value;
                }
            }
        }
        /// <summary>
        /// 参数列表，用于存放参数
        /// </summary>
        public struct sttSqlParameterList
        {
            private string sName;
            private string sType;
            private string sValue;

            /// <summary>
            /// 构造函数(初始化值)
            /// </summary>
            /// <param name="asName">参数名</param>
            /// <param name="asType">参数类型(暂时只针对string型的值)</param>
            /// <param name="asValue">参数值</param>
            public sttSqlParameterList(string asName, string asType, string asValue)
            {
                sName = asName;
                sType = asType;
                sValue = asValue;
            }

            /// <summary>
            /// 参数名(与SQL语句中的参数名相同,但不带:号)
            /// </summary>
            public string Name
            {
                get
                {
                    return sName;
                }
                set
                {
                    sName = value;
                }
            }

            /// <summary>
            /// 参数类型(本参数暂时只接受string型)
            /// </summary>
            public string Type
            {
                get
                {
                    return sType;
                }
                set
                {
                    sType = value;
                }
            }

            /// <summary>
            /// 参数值,与参数类型对应
            /// </summary>
            public string Value
            {
                get
                {
                    return sValue;
                }
                set
                {
                    sValue = value;
                }
            }
        }

        /// <summary>
        /// 执行事务(传入语句列表)
        /// </summary>
        /// <param name="sSqlList">一次执行的多条语句列表</param>
        public  int ExecParameterTransaction(List<sttSqlList> aSqlList)
        {
            //影响的值
            int iExecCou = 0;
          
            //创建并开启事务
            using (OracleTransaction oraTrans = con.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                OracleCommand ocCommand = con.CreateCommand();
                ocCommand.Transaction = oraTrans;
                try
                {
                    //执行多表SQL
                    foreach (sttSqlList sttItemSql in aSqlList)
                    {
                        //清除参数(为执行下一条语句作准备)
                        ocCommand.Parameters.Clear();
                        //要执行的语句
                        ocCommand.CommandText = sttItemSql.atbSQl;
                        sttSqlParameterList[] sttPraList = sttItemSql.atbSqlPraList;
                        for (int i = 0; i < sttPraList.Length; i++)
                        {
                            OracleParameter opName = new OracleParameter();
                            opName.ParameterName = sttPraList[i].Name;
                            switch (sttPraList[i].Type)
                            {
                                case "string":
                                    opName.OracleType = OracleType.VarChar;
                                    break;
                                default:
                                    opName.OracleType = OracleType.VarChar;
                                    break;
                            }
                            opName.Value = sttPraList[i].Value;
                            //增加参数
                            ocCommand.Parameters.Add(opName);
                        }
                        //执行
                        iExecCou += ocCommand.ExecuteNonQuery();
                    }

                    //完成后确认事务
                    oraTrans.Commit();
                }
                catch (Exception ex)
                {
                    //出错后回滚事务
                    oraTrans.Rollback();
                    HandleExceptions(ex);
                }
                finally
                {
                    ocCommand.Dispose();
                    con.Close();
                }
            }
            return iExecCou;
        }

        #endregion
    }

    #region 数据类型
    /// <summary>
    /// 数据类型类型枚举 Decimal（金钱）,Varchar（字符串）,DateTime（时间）, Iamge（图片),Int（整型）,Text（文本）
    /// </summary>
    /// <param name="Decimal">金钱</param>
    /// <param name="Varchar">字符串</param>
    /// <param name="DateTime">时间</param>
    /// <param name="Iamge">图片</param>
    /// <param name="Int">整型</param>
    /// <param name="Text">文本</param>
    public enum Mytype
    {
        Decimal,
        Varchar,
        DateTime,
        Iamge,
        Int,
        Text
    } 
    #endregion

}
